/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.webos.java.base.hsqldb.migration;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.webos.java.root.Log;

/**
 *
 * @author Kostya
 */
public class DBDefinition {
    private List<TableDefinition> tableDefinitions = new ArrayList<TableDefinition>();

    public void addTable(TableDefinition aDefinition){
        tableDefinitions.add(aDefinition);
    }

    public int getTableCount(){
        return tableDefinitions.size();
    }

    public TableDefinition getTable(int index){
        return tableDefinitions.get(index);
    }

    public boolean migrateDB(Connection aConnection){
        try {
            Log.debug(this, "migrateDB started");
            List<String> queries = new ArrayList<String>();
            List<String> tables = new ArrayList<String>();
            ResultSet rset = aConnection.getMetaData().getTables(null, null, null, null);
            while(rset.next()){
                String tableName = rset.getString("TABLE_NAME");
                tables.add(tableName);
            }
            Log.debug(this, "Number of tables found: "+tables.size());
            for (int i = 0; i < getTableCount(); i++) {
                TableDefinition table = getTable(i);
                boolean createTable = true;
                for (String string : tables) {
                    if(string.equalsIgnoreCase(table.getName())){
                        createTable = false;
                        break;
                    }
                }
                if(createTable){
                    StringBuffer buffer = new StringBuffer();
                    buffer.append("CREATE ");
                    if(table.getType()!=null)
                        buffer.append(table.getType()+" ");

                    buffer.append("TABLE "+table.getName()+" (");
                    for (int j = 0; j < table.getRowCount(); j++) {
                        RowDefinition row = table.getRow(j);
                        if(j>0)
                            buffer.append(", ");
                        buffer.append(row.getQuery());
                    }
                    for (int j = 0; j < table.getRowCount(); j++) {
                        RowDefinition row = table.getRow(j);
                        if(row.isPrimary()){
                            buffer.append(", PRIMARY KEY ("+row.getName()+")");
                        }
                        if(row.getReference()!=null){
                            buffer.append(", FOREIGN KEY ("+row.getName()+") REFERENCES "+row.getReference().getTable().getName()+" ("+row.getReference().getName()+")");
                        }
                    }
                    buffer.append(")");
                    queries.add(buffer.toString());
                    if("TEXT".equalsIgnoreCase(table.getType())){
                        queries.add("SET TABLE "+table.getName()+" SOURCE \""+table.getName().toLowerCase()+"\"");
                    }
                }else{
                    rset = aConnection.getMetaData().getColumns(null, null, table.getName().toUpperCase(), null);
                    List<String> rows = new ArrayList<String>();
                    while(rset.next()){
                        String rowName = rset.getString("COLUMN_NAME");
//                        Log.debug(this, table.getName()+" column name: "+rowName);
                        rows.add(rowName);
                    }
                    Log.debug(this, "Rows for table "+table.getName()+": "+rows.size());
                    for (int j = 0; j < table.getRowCount(); j++) {
                        RowDefinition row = table.getRow(j);
                        boolean addRow = true;
                        for (String string : rows) {
                            if(string.equalsIgnoreCase(row.getName())){
                                addRow = false;
                                break;
                            }
                        }
                        if(addRow){
                            queries.add("ALTER TABLE "+table.getName()+" ADD "+row.getQuery());
                            if(row.isPrimary()){
                                queries.add("ALTER TABLE "+table.getName()+" ADD " +
                                        "PRIMARY KEY ("+row.getName()+")");
                            }
                            if(row.getReference()!=null){
                                queries.add("ALTER TABLE "+table.getName()+" ADD " +
                                        "FOREIGN KEY ("+row.getName()+") " +
                                        "REFERENCES "+row.getReference().getTable().getName()+" " +
                                        "("+row.getReference().getName()+")");
                            }
                        }
                    }
                }
            }
            Log.debug(this, "Queries ready to execute ["+queries.size()+"]:");
            Statement st = aConnection.createStatement();
            for (String string : queries) {
                Log.debug(this, string);
                st.execute(string);
            }
            return true;
        } catch (Throwable t) {
            Log.error(this, "Exception thrown: " + t.getMessage());
            t.printStackTrace();
        }
        return false;
    }
}
